﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using TpOffice.Forms;
using TpOffice.Interfaces;
using TecBaseObjects.Base;
using TecBaseObjects.Models;
using TecContracts;
using TecData.Manager;
using TecTools;
using TecBaseObjects.Interfaces;
using TecExtensions;

namespace TpOffice.Controller {

    public class ImportBMDController : IController {

        #region Members

        ImportBMDForm myForm;
        private const string errType = "Err";
        private const string logType = "Log";
        private const string logArea = "TpAccounting.Controller.ImportBMDController";
        //private TpAccountingManager dataManager = new TpAccountingManager();
        private int errLog = 0;
        private string s1Address;
        private string s2Address;
        private string s3Address;
        private Microsoft.Office.Interop.Excel.Application exApp;

        #endregion

        #region Constructor

        public ImportBMDController(ITecForm form) {
            myForm = form as ImportBMDForm;
        }

        #endregion

        #region Methods

        public void BeforeClosing(object sender, FormClosingEventArgs e) {
            //DialogResult ok = MessageBox.Show("Wollen Sie das Formular wirklich schließen?", "Import BMD", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            //if (ok == DialogResult.No) {
            //    e.Cancel = true;
            //}
        }

        private void ChangeSign(Worksheet sh) {
            int startRow = 2;
            bool weiter = false;
            double d1 = 0.0;
            double d2 = 0.0;

            Range r1;
            Range r2;
            while (weiter == false) {
                r1 = sh.Cells[startRow, "A"];
                if (r1.Value != null) {
                    r2 = sh.Cells[startRow, "G"];
                    if (r2.Value != null) {
                        double.TryParse(r2.Value.ToString(), out d1);
                        d2 = d1 * -1;
                        r2.Value = d2;
                    }
                    startRow++;
                } else {
                    weiter = true;
                }
            }
            Range sumRow = sh.Cells[startRow, "B"];
            //sumRow.Value = "Summe";
            sumRow = sh.Cells[startRow, "F"];
            sumRow.FormulaR1C1 = "=Sum(R2C6:R" + (startRow - 1).ToString() + "C6)";
            sumRow.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
            s1Address = "R" + startRow.ToString() + "C6";

            sumRow = sh.Cells[startRow, "G"];
            sumRow.FormulaR1C1 = "=Sum(R2C7:R" + (startRow - 1).ToString() + "C7)";
            sumRow.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
            s2Address = "R" + startRow.ToString() + "C7";

            sumRow = sh.Cells[startRow, "I"];
            sumRow.FormulaR1C1 = "=Sum(R2C9:R" + (startRow - 1).ToString() + "C9)";
            sumRow.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
            s3Address = "R" + startRow.ToString() + "C9";

            Range row = sh.Rows[startRow, Missing.Value];
            Office.FormatRowAsSummaryRow(row, true);
        }

        private void CopyRowsAndSetValues(Worksheet source, Worksheet target, string company) {
            int startRow = 2;
            int copyRow = 2;
            int sumRow = 3;
            int rowCount = 2;
            bool weiter = false;
            bool firstRow = true;
            List<string> summenDebit = new List<string>();
            List<string> summenCredit = new List<string>();

            Cursor curCursor = myForm.Cursor;

            myForm.Cursor = Cursors.WaitCursor;

            ProgressBar pb = myForm.Controls["pgBarIsInUse"] as ProgressBar;

            #region RowsCount

            while (rowCount < 10000) {
                Range r1 = source.Cells[rowCount, "A"];
                if (r1.Value != null) {
                    rowCount++;
                    continue;
                }
                break;
            }

            #endregion

            try {
                while (startRow < 10000 && weiter == false) {
                    Range r1 = source.Rows[startRow];
                    Range r2 = source.Rows[startRow + 1];

                    TpAccountingSourceRow curRow = new TpAccountingSourceRow(r1);
                    TpAccountingSourceRow nextCur = new TpAccountingSourceRow(r2);

                    #region Empty - Gesamtsummenzeile

                    if (curRow.IsEmpty) {
                        //First Summary Row
                        Range s1 = target.Cells[copyRow, "E"];

                        string sumFormel = "";
                        foreach (string feld in summenDebit) {
                            sumFormel += (sumFormel.Length > 0 ? "+" + feld : feld);
                        }

                        s1.Value = "=" + sumFormel;
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        sumFormel = "";
                        foreach (string feld in summenCredit) {
                            sumFormel += (sumFormel.Length > 0 ? "+" + feld : feld);
                        }

                        s1 = target.Cells[copyRow, "F"];
                        s1.Value = "=" + sumFormel;
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        s1 = target.Cells[copyRow, "H"];
                        s1.FormulaR1C1 = "=Sum(R2C8:R" + (copyRow - 1).ToString() + "C8)";
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        s1 = target.Cells[copyRow, "I"];
                        s1.FormulaR1C1 = "=Sum(R2C9:R" + (copyRow - 1).ToString() + "C9)";
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        s1 = target.Cells[copyRow, "J"];
                        s1.FormulaR1C1 = "=Sum(R2C10:R" + (copyRow - 1).ToString() + "C10)";
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        s1 = target.Cells[copyRow, "K"];
                        s1.FormulaR1C1 = "=Sum(R2C11:R" + (copyRow - 1).ToString() + "C11)";
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        s1 = target.Cells[copyRow, "L"];
                        s1.FormulaR1C1 = "=Sum(R2C12:R" + (copyRow - 1).ToString() + "C12)";
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        s1 = target.Rows[copyRow, Missing.Value];
                        Office.FormatRowAsSummaryRow(s1, false);

                        s1 = target.Cells[copyRow, "A"];
                        s1.Value = "Summe";



                        #region Format

                        Range col1 = target.Columns["E:G"];
                        col1.NumberFormat = "#,##0.00";

                        col1 = target.Columns["A"];
                        col1.HorizontalAlignment = Constants.xlLeft;

                        col1 = target.Columns["I:L"];
                        col1.NumberFormat = "#,##0.00";

                        if (target.Name.Contains("_dk") || target.Name.Contains("_kk")) {
                            col1 = target.Columns["M"];
                            col1.NumberFormat = Extensions.tpDateFormat;
                            col1.HorizontalAlignment = Constants.xlLeft;
                        } else {
                            col1 = target.Columns["M"];
                            col1.NumberFormat = "#,##0.00";
                        }

                        col1 = target.Columns["N:Z"];
                        col1.NumberFormat = "#,##0.00";

                        #endregion

                        copyRow++;

                        //Second Summary Row
                        s1 = target.Cells[copyRow, "A"];
                        s1.Value = "Differenz";


                        s1 = target.Cells[copyRow, "E"];
                        s1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C5-" + source.Name + "!" + s1Address;
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent3;

                        s1 = target.Cells[copyRow, "F"];
                        s1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C6-" + source.Name + "!" + s2Address;
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent3;

                        s1 = target.Cells[copyRow, "H"];
                        s1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C8-" + source.Name + "!" + s3Address;
                        s1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent3;

                        s1 = target.Rows[copyRow, Missing.Value];
                        Office.FormatRowAsSummaryRow(s1, true);

                        return;
                    }

                    #endregion

                    #region Kontozeile

                    if (firstRow && !curRow.IsEmpty) {
                        Range t1 = target.Cells[copyRow, "A"];
                        t1.Value = "Konto";
                        t1.Font.Italic = true;

                        t1 = target.Cells[copyRow, "B"];
                        t1.NumberFormat = "0## ##";
                        if (curRow.AccountNo > 99999) {
                            t1.NumberFormat = "### ###";
                        }

                        t1.Value = curRow.AccountNo;
                        t1.HorizontalAlignment = Constants.xlLeft;
                        t1.Font.Italic = true;

                        t1 = target.Cells[copyRow, "C"];
                        string account = "";
                        account = TpAccountingDataManager.GetAccountName(curRow.AccountNo.ToString(), company);
                        if (account == "") {
                            account = "Unbekannt";
                        }
                        t1.Value = account;
                        t1.Font.Italic = true;

                        copyRow++;
                        firstRow = false;
                    }

                    #endregion

                    #region Buchungszeile

                    Range r3 = target.Rows[copyRow];
                    TpAccountingTargetRow tarRow = new TpAccountingTargetRow(exApp, r3);
                    tarRow.AccountNo = curRow.ContraAccountNo;
                    tarRow.Credit = curRow.Credit;
                    tarRow.Debit = curRow.Debit;
                    tarRow.Tax = curRow.Tax;
                    tarRow.TaxKey = curRow.TaxKey;
                    tarRow.Text = curRow.Text;
                    tarRow.VoucherDate = curRow.VoucherDate;
                    tarRow.VoucherNo = curRow.VoucherNo;

                    if (curRow.AccountNo == nextCur.AccountNo) {
                        //r3.EntireRow.Hidden = true;  //auskommentiert 14-03-25 Anweisung von Wk
                    }

                    pb.Value = Convert.ToInt32(Convert.ToDouble(startRow) / Convert.ToDouble(rowCount) * 100.0);

                    #endregion

                    #region Summen

                    if (curRow.AccountNo != nextCur.AccountNo) {
                        copyRow++;
                        Range t1 = target.Cells[copyRow, "A"];
                        t1.Value = "Summen";
                        t1.Font.Bold = true;

                        t1 = target.Cells[copyRow, "E"];
                        t1.FormulaR1C1 = "=Sum(R" + sumRow.ToString() + "C5:R" + (copyRow - 1).ToString() + "C5)";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
                        summenDebit.Add(t1.Address);

                        t1 = target.Cells[copyRow, "F"];
                        t1.FormulaR1C1 = "=Sum(R" + sumRow.ToString() + "C6:R" + (copyRow - 1).ToString() + "C6)";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
                        summenCredit.Add(t1.Address);

                        t1 = target.Cells[copyRow, "G"];
                        t1.FormulaR1C1 = "=R" + copyRow.ToString() + "C5-R" + copyRow.ToString() + "C6";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        Range t2 = target.Cells[copyRow, "I"];
                        Range t3 = target.Cells[copyRow, "E"];
                        Range t4 = target.Cells[copyRow, "F"];

                        t2.Value = (Convert.ToDouble(t3.Value) - Convert.ToDouble(t4.Value));
                        t2.Font.Bold = true;

                        t2 = target.Cells[copyRow, "J"];
                        t2.FormulaR1C1 = "=R" + copyRow.ToString() + "C9-R" + copyRow.ToString() + "C7";
                        t2.Font.Bold = true;
                        t2.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        //=============  Next Row ==================
                        copyRow++;
                        t1 = target.Rows[copyRow, Missing.Value];
                        t1.Font.Bold = true;
                        t1 = target.Cells[copyRow, "G"];
                        t1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C7+R" + copyRow.ToString() + "C5-R" + copyRow.ToString() + "C6";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        //=============  Next Row ==================
                        copyRow++;
                        t1 = target.Rows[copyRow, Missing.Value];
                        t1.Font.Bold = true;
                        t1 = target.Cells[copyRow, "G"];
                        t1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C7+R" + copyRow.ToString() + "C5-R" + copyRow.ToString() + "C6";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        //=============  Next Row ==================
                        copyRow++;
                        t1 = target.Rows[copyRow, Missing.Value];
                        t1.Font.Bold = true;
                        t1 = target.Cells[copyRow, "G"];
                        t1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C7+R" + copyRow.ToString() + "C5-R" + copyRow.ToString() + "C6";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        //=============  Next Row ==================
                        copyRow++;
                        t1 = target.Rows[copyRow, Missing.Value];
                        t1.Font.Bold = true;
                        t1 = target.Cells[copyRow, "G"];
                        t1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C7+R" + copyRow.ToString() + "C5-R" + copyRow.ToString() + "C6";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        //============= Last Summen Row =======================
                        copyRow++;
                        t1 = target.Rows[copyRow, Missing.Value];
                        t1.Font.Bold = true;
                        t1 = target.Cells[copyRow, "G"];
                        target.Names.Add("konto" + curRow.AccountNo.ToString().Trim(), t1);
                        t1.FormulaR1C1 = "=R" + (copyRow - 1).ToString() + "C7+R" + copyRow.ToString() + "C5-R" + copyRow.ToString() + "C6";
                        t1.Font.Bold = true;
                        t1.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        t2 = target.Cells[copyRow, "K"];
                        //t2.Value = 0.0;
                        t2.Font.Bold = true;

                        t2 = target.Cells[copyRow, "L"];
                        t2.Value = "=R" + copyRow.ToString() + "C11-R" + copyRow.ToString() + "C7";
                        t2.Font.Bold = true;
                        t2.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;

                        sumRow = copyRow + 2;
                        firstRow = true;
                    }

                    #endregion

                    startRow++;
                    copyRow++;

                }
            } catch (Exception ex) {
                errLog += 1;
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyRowsAndSetValues: " + ex.Message, true);
            } finally {
                myForm.Cursor = curCursor;
            }
        }

        private void CreateNewImportSheet(Workbook wb, Worksheet sh, string company) {
            Worksheet newImport = wb.Worksheets.Add(After: sh);
            if (wb.Name.IndexOf(".") > 0) {
                newImport.Name = wb.Name.Left((byte)wb.Name.IndexOf("."));
            } else {
                newImport.Name = wb.Name;
            }

            #region Settings

            //Default-Settings
            Window w = exApp.ActiveWindow;
            w.DisplayGridlines = false;
            w.SplitColumn = 0;
            w.SplitRow = 1;
            w.FreezePanes = true;

            #endregion

            try {

                #region Sheet

                DeleteColumns(sh);
                ChangeSign(sh);
                SetHeaderNames(wb, newImport);
                CopyRowsAndSetValues(sh, newImport, company);

                #endregion

            } catch (Exception ex) {
                errLog += 1;
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateNewImportSheet: " + ex.Message, true);
            }


        }

        private void DeleteColumns(Worksheet sh) {
            Range col = sh.Columns["A:B"];
            //col.Select();
            col.Delete(Shift: XlDirection.xlToLeft);
        }

        public void Dispose() {
        }

        public void ImportFileHandling(object sender, EventArgs e, string fileName, string company) {
            this.exApp = Office.GetExcelApplication();
            Workbook wb = null;
            try {
                errLog = 0;
                if (!CheckImportFile(fileName)) {
                    return;
                }

                wb = exApp.Workbooks.Open(fileName);

                Worksheet sh = wb.Worksheets[1];

                sh.Name = "Import";

                CreateNewImportSheet(wb, sh, company);

                this.myForm.ReloadData(GetMySheets(wb));

            } catch (Exception ex) {
                errLog += 1;
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ImportFileHandling: " + ex.Message, true);
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close(SaveChanges: true);
                }
            }

            if (errLog == 0) {
                MessageBox.Show("Es wurde ein neues Arbeitsblatt erfolgreich hinzugefügt!", "TpOffice", MessageBoxButtons.OK, MessageBoxIcon.Information);
            } else {
                MessageBox.Show("Leider ist ein Fehler aufgetreten! Kontaktieren Sie den Administrator.", "TpOffice", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        public void OpenFile(object sender, EventArgs e, string fileName) {
            Microsoft.Office.Interop.Excel.Application exApp = Office.GetExcelApplication();
            try {
                if (!CheckImportFile(fileName)) {
                    return;
                }

                Workbook wb = exApp.Workbooks.Open(fileName);
                exApp.Visible = true;

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ImportFileHandling: " + ex.Message, true);
            }
        }

        public void Save(IBaseObject myObject) {
            throw new NotImplementedException("MainController.Save");
        }

        private void SetHeaderNames(Workbook wb, Worksheet sh) {

            wb.Theme.ThemeColorScheme.Load(@"H:\Vorlagen\Document Themes\Theme Colors\Torprofi.xml");

            Range r2 = sh.Rows[1, Missing.Value];
            r2.Font.Italic = true;

            SetColumnBorder(sh, "H");
            SetColumnBorder(sh, "L");

            Range r1 = sh.Cells[1, 1];
            r1.Value = "Datum";

            r1 = sh.Cells[1, 2];
            r1.Value = "Beleg";

            r1 = sh.Cells[1, 3];
            r1.Value = "Buchungstext";

            r1 = sh.Cells[1, 4];
            r1.Value = "Kto";

            r1 = sh.Cells[1, 5];
            r1.Value = "Soll";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 6];
            r1.Value = "Haben";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 7];
            r1.Value = "Ust";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 8];
            r1.Value = "St";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 9];
            r1.Value = "Saldo";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 10];
            r1.Value = "Diff.";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 11];
            r1.Value = "Saldo";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            r1 = sh.Cells[1, 12];
            r1.Value = "Diff.";
            r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

            if (wb.Name.Contains("_dk") || wb.Name.Contains("_kk")) {
                r1 = sh.Cells[1, 13];
                r1.Value = "Datum";
                r1.HorizontalAlignment = XlHAlign.xlHAlignLeft;

                r1 = sh.Cells[1, 14];
                r1.Value = "Zahlung";
                r1.HorizontalAlignment = XlHAlign.xlHAlignRight;

                SetColumnBorder(sh, "N");
            }
        }

        private void SetColumnBorder(Worksheet sh, string col) {
            Range r1 = sh.Columns[col];
            r1.Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
            r1.Borders[XlBordersIndex.xlEdgeRight].Weight = 1;
        }

        #endregion

        #region Functions

        private bool CheckImportFile(string fileName) {
            bool ret = true;
            if (!File.Exists(fileName)) {
                string msg = String.Format("Datei ({0} ist nicht vorhanden!", fileName);
                MessageBox.Show(msg, myForm.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            if (Tools.FileIsInUse(fileName)) {
                string msg = String.Format("Datei ({0} wird gerade verwendet und ist gesperrt!", fileName);
                MessageBox.Show(msg, myForm.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return ret;
        }

        private string GetAccountName(string accountNo, string company) {
            return TpAccountingDataManager.GetAccountName(accountNo, company);
        }

        public string GetCompany(string fileName) {
            string ret = fileName.Substring(3, fileName.IndexOf(@"\", 3) - 3);
            return ret;
        }

        public string GetImportFileName(object sender, EventArgs e) {
            string ret = "";
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK) {
                ret = ofd.FileName;
            }
            return ret;
        }

        public List<ImportBMDModel> GetMySheets(Workbook wb) {
            List<ImportBMDModel> myModel = new List<ImportBMDModel>();
            foreach (Worksheet sh in wb.Worksheets) {
                ImportBMDModel ibm = new ImportBMDModel();
                ibm.MySheet = sh.Name;
                myModel.Add(ibm);
            }
            return myModel;
        }

        public List<ImportBMDModel> GetMySheets(string importFileName) {
            List<ImportBMDModel> myModel = new List<ImportBMDModel>();
            Microsoft.Office.Interop.Excel.Application exApp = Office.GetExcelApplication();

            try {
                if (!File.Exists(importFileName)) {
                    string msg = String.Format("Datei ({0} ist nicht vorhanden!", importFileName);
                    MessageBox.Show(msg, myForm.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return myModel;
                }

                if (Tools.FileIsInUse(importFileName)) {
                    string msg = String.Format("Datei ({0} wird gerade verwendet und ist gesperrt!", importFileName);
                    MessageBox.Show(msg, myForm.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return myModel;
                }

                Workbook wb = exApp.Workbooks.Open(importFileName);

                foreach (Worksheet sh in wb.Worksheets) {
                    ImportBMDModel ibm = new ImportBMDModel();
                    ibm.MySheet = sh.Name;
                    myModel.Add(ibm);
                }

                wb.Close(SaveChanges: false);


            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetMySheets: " + ex.Message, true);
            }

            return myModel;
        }

        #endregion

    }
}
